package com.bionic.banking.dao.connection;

import com.bionic.banking.dao.AbstractDao;
import java.sql.*;
import java.util.*;
import javax.naming.NamingException;
import org.apache.log4j.*;

public final class DBQueryManager<T> {

    private static final Logger logger = LoggerLoader.getLogger(DBQueryManager.class);
    private int maxRows = 100;          //records per page  
    private AbstractDao<T> dbObject;    //one instance of pool for all DB queries

    public DBQueryManager(AbstractDao<T> daoObject) {
        dbObject = daoObject;
    }
    private static ConnectionPool connPool;

    private static Connection getConnection() throws SQLException, NamingException {
        //connPool = ConnectionPool.getInstance();
        //return connPool.getConnection();
        return JDBCConnectionFactory.getInstance().getConnection(); // old implementation
    }

    private static void freeConnection(Connection connection) throws SQLException {
        //if (connPool != null) {
        //    connPool.freeConnection();
        //}
        if (connection != null) {  // old implementation
            connection.close();
        }
    }

    public T populateDto(ResultSet rs) throws SQLException {
        return dbObject.populateDto(rs);
    }

    /**
     * Create entity in DB and return new entity ID
     */
    public Integer executeCreateQuery(String sql, Object[] sqlParams) throws SQLException {
        Connection connection = null;
        PreparedStatement ptmt = null;
        final String query = sql;
        boolean result = false;
        Integer newEntityId = -1;
        try {
            connection = getConnection();
            ptmt = connection.prepareStatement(query);

            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                ptmt.setObject(i + 1, sqlParams[i]);
            }
            result = ptmt.executeUpdate() > 0;
            if (result) {
                ResultSet rs = ptmt.executeQuery("SELECT LAST_INSERT_ID(); ");
                if (rs != null) {
                    while (rs.next()) {
                        newEntityId = rs.getInt(1);// if record is created - get its new Id 
                        //or entity = populateDto(rs); - update newly created filds: id, etc 
                    }
                }
            }
        } catch (SQLException | NamingException ex) {
            logger.error(ex);
            throw new SQLException(ex.getMessage());
        } finally {
            if (ptmt != null) {
                ptmt.close();
            }
            freeConnection(connection);
            return newEntityId;
        }
    }

    /**
     * Update entity in DB
     */
    public Boolean executeSaveQuery(String sql, Object[] sqlParams) throws SQLException {
        Connection connection = null;
        PreparedStatement ptmt = null;
        final String query = sql;
        boolean result = false;

        try {
            connection = getConnection();
            ptmt = connection.prepareStatement(query);

            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                ptmt.setObject(i + 1, sqlParams[i]);
            }
            result = ptmt.executeUpdate() > 0;
        } finally {
            if (ptmt != null) {
                ptmt.close();
            }
            freeConnection(connection);
            return result;
        }
    }

    public boolean executeRemoveQuery(String sql, Integer id) throws SQLException {
        Connection connection = null;
        PreparedStatement ptmt = null;        
        try {
            connection = getConnection();
            ptmt = connection.prepareStatement(sql);
            ptmt.setObject(1, id);
            int res = ptmt.executeUpdate();
            if (res > 0) {
                return true;
            }
        } catch (NamingException ex) {
            logger.error(ex, ex);
            throw new SQLException("Naming container exception: ", ex);
        } finally {
            if (ptmt != null) {
                ptmt.close();
            }
            freeConnection(connection);
        }
        return false;
    }

    public List<T> executeSelectQuery(String sql, Object[] sqlParams) throws SQLException {
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //rs = executeResultQuery(sql, sqlParams); or
            connection = getConnection();
            final String SQL = sql;
            stmt = connection.prepareStatement(SQL);
            stmt.setMaxRows(maxRows);

            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }
            rs = stmt.executeQuery();
            return fetchMultiResults(rs);

        } catch (NamingException ex) {
            logger.error(ex);
            throw new SQLException(ex.getMessage());
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            freeConnection(connection);
        }
    }

    public T executeSelectSingleQuery(String sql, Object[] sqlParams) throws SQLException {
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //rs = executeResultQuery(sql, sqlParams); or
            connection = getConnection();
            final String SQL = sql;
            stmt = connection.prepareStatement(SQL);
            stmt.setMaxRows(maxRows);
            // bind parameters
            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }
            rs = stmt.executeQuery();

            return fetchSingleResult(rs);
        } catch (NamingException ex) {
            logger.error(ex);
            throw new SQLException(ex.getMessage());
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            freeConnection(connection);
        }
    }

    private T fetchSingleResult(ResultSet rs) throws SQLException {
        if (rs.next()) {
            T dto = populateDto(rs);
            return dto;
        } else {
            return null;
        }
    }

    private List<T> fetchMultiResults(ResultSet rs) throws SQLException {
        List<T> resultList = new ArrayList<>();
        while (rs.next()) {
            T dto = populateDto(rs);
            resultList.add(dto);
        }
        return resultList;
    }

    public static ResultSet executeResultQuery(String sql, Object[] sqlParams) throws SQLException {
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            connection = getConnection();
            final String SQL = sql;
            stmt = connection.prepareStatement(SQL);

            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }
            rs = stmt.executeQuery();
        } catch (NamingException ex) {
            logger.error(ex, ex);
            throw new SQLException("Exception: " + ex.getMessage(), ex);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            freeConnection(connection);
        }
        return rs;
    }
}